"""
  统计分析模块(数据库操作)
"""
from models import *
from sqlalchemy.orm import Session, joinedload, aliased
from schema import *
from sqlalchemy import func, select, text


# 查询所有超过30岁的学员的信息
def get_stu_info_by_age(age:int, db:Session):
    return db.query(Student_db).filter(Student_db.age >= age).all()

# 统计每个班级男生女生的人数
def get_count_gender(cid:str,db:Session):
   result = (db.query(Student_db.gender,func.count(Student_db.student_id)).filter(Student_db.class_id == cid).group_by(Student_db.gender).all())
   return [{"gender":gender,"count":count} for gender,count in result]

# 查询每次考试成绩都在80分以上的学生的编号，姓名和成绩
def get_sinfo_by_grade(sc:int,db:Session):
    subquery = (
        db.query(Stu_score_db.student_id).group_by(Stu_score_db.student_id).having(func.min(Stu_score_db.score) >= sc).subquery()
    )
    sb = aliased(Stu_score_db)
    result = (
        db.query(Student_db.student_id,Student_db.student_name,sb.round,sb.score).join(subquery,Student_db.student_id == subquery.c.student_id).join(sb,sb.student_id == Student_db.student_id).all()
    )
    return [{"student_id":student_id,"student_name":student_name,"round":round,"score":score} for student_id,student_name,round,score in result]

# 查询有两次以上不及格的学生的姓名，班级和不及格成绩
def grade_2fail_info(ct:int,db:Session):
    subquery = (
        db.query(Stu_score_db.student_id).filter(Stu_score_db.score < 60).group_by(Stu_score_db.student_id).having(func.count(Stu_score_db.student_id) >= ct).subquery()
    )
    sb = aliased(Stu_score_db)
    result = (
        db.query(Student_db.student_name,Student_db.class_id,sb.round,sb.score).join(subquery,Student_db.student_id==subquery.c.student_id).join(sb,sb.student_id==Student_db.student_id).all()
    )

    return [{"student_id":student_id,"student_name":student_name,"round":round,"score":score} for student_id,student_name,round,score in result]

# 统计每次考试每个班级的平均分，按照从高到低排序
def statistic_avg_grade_by_class(db:Session):
  subquery = (
      db.query(Student_db.class_id,Stu_score_db.round,func.avg(Stu_score_db.score).label("avg_score")).join(Stu_score_db,Student_db.student_id == Stu_score_db.student_id).group_by(Student_db.class_id,Stu_score_db.round).subquery()
  )

  result = (
      db.query(subquery).order_by(subquery.c.avg_score.desc()).all()
  )
  return [{"class_id":class_id,"round":round,"avg_score":avg_score} for class_id,round,avg_score in result]

# 统计就业薪资最高的前五名学生的姓名，班级和就业时间，就业公司
def statistic_stu_by_sal(db:Session):
    result = (
        db.query(Student_db.student_name,Student_db.class_id,Stu_employment_db.offer_time,Stu_employment_db.company,Stu_employment_db.salary).join(Stu_employment_db,Student_db.student_id == Stu_employment_db.student_id).order_by(Stu_employment_db.salary.desc()).limit(5).all()
    )

    return [{"student_name":student_name,"class_id":class_id,"offer_time":offer_time,"company":company,"salary":salary} for student_name,class_id,offer_time,company,salary in result]

# 统计每个学生的就业时长（offer下发时间-就业开放时间）
def statistic_find_job_time(db:Session):
    result = (
         db.query(Stu_employment_db.student_id,
                  func.timestampdiff(text("DAY"),Stu_employment_db.open_time,Stu_employment_db.offer_time).label("fjob_time_days")).all()
    )
    return [{"student_id":student_id,"fjob_time_days":fjob_time_days} for student_id,fjob_time_days in result]

# 统计每个班级的平均就业时长（只统计进入就业阶段的学生，也就是有就业开放时间）
def class_avg_job_time(db:Session):
    subquery = (
        db.query(Stu_employment_db.student_id,
                 func.timestampdiff(text("DAY"), Stu_employment_db.open_time, Stu_employment_db.offer_time).label(
                     "fjob_time_days")).filter(Stu_employment_db.open_time.isnot(None)).subquery()
    )

    result = (
        db.query(Student_db.class_id,func.avg(subquery.c.fjob_time_days).label("avg_time")).join(subquery,Student_db.student_id == subquery.c.student_id).group_by(Student_db.class_id).all()
    )

    return [{"class_id":class_id,"avg_time":avg_time} for class_id,avg_time in result]